﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using IdeaDomain.InfrastructureLayer.DataEntities;
using UtilityComponent.DataFactory;

namespace IdeaDomain.InfrastructureLayer.DataManagers
{
    public class IdeaManager
    {
        private IDataAccess _dataAccess;

        public IdeaManager()
        {
            _dataAccess = DataAccessFactory.CreateDataAccess();
        }

        /// <summary>
        /// Get ideas with columns by userid.
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public List<IdeaDE> GetIdeasByUser(SqlCommand command, int userId)
        {
            DataTable ideasFromTable = new DataTable();
            List<IdeaDE> ideaDEList = new List<IdeaDE>();
            string querysql_idea = "SELECT * FROM [Idea] WHERE UserId = @userId and IsDeleted = 0";
            QueryParameter[] paramList_idea = new QueryParameter[1];
            paramList_idea[0] = new QueryParameter("@userId", userId, DbType.Int32);
            ideasFromTable = _dataAccess.GetTable(command, querysql_idea, paramList_idea);
            if (ideasFromTable.Rows.Count > 0)
            {
                foreach (DataRow row in ideasFromTable.Rows)
                {
                    IdeaDE ideaDE = new IdeaDE()
                    {
                        IdeaId = Convert.ToInt32(row["IdeaId"]),
                        IdeaName = Convert.ToString(row["IdeaName"]),
                        IdeaDescription = Convert.ToString(row["IdeaDescription"]),
                        CreateTime = Convert.ToDateTime(row["CreateTime"]),
                        IsDeleted = Convert.ToBoolean(row["IsDeleted"]),
                        UserId = Convert.ToInt32(row["UserId"])
                    };
                    ideaDEList.Add(ideaDE);
                }
                return ideaDEList;
            }
            return null;

        }

        /// <summary>
        /// Get an IdeaDE entity by ideaid.
        /// </summary>
        /// <param name="ideaId"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public IdeaDE GetIdeaById(SqlCommand command, int ideaId)
        {
            DataTable ideasFromTable = new DataTable();
            string querysql_idea = "SELECT * FROM [Idea] WHERE IdeaId = @ideaId and IsDeleted = 0";
            QueryParameter[] paramList_idea = new QueryParameter[1];
            paramList_idea[0] = new QueryParameter("@ideaId", ideaId, DbType.Int32);
            ideasFromTable = _dataAccess.GetTable(command, querysql_idea, paramList_idea);
            if (ideasFromTable.Rows.Count > 0)
            {
                DataRow row = ideasFromTable.Rows[0];
                IdeaDE ideaDE = new IdeaDE()
                {
                    IdeaId = Convert.ToInt32(row["IdeaId"]),
                    IdeaName = Convert.ToString(row["IdeaName"]),
                    IdeaDescription = Convert.ToString(row["IdeaDescription"]),
                    CreateTime = Convert.ToDateTime(row["CreateTime"]),
                    IsDeleted = Convert.ToBoolean(row["IsDeleted"]),
                    UserId = Convert.ToInt32(row["UserId"]),
                };
                return ideaDE;
            }
            return null;
        }

        /// <summary>
        /// Get an IdeaDE entity by ideaid.
        /// </summary>
        /// <param name="ideaId"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public IdeaDE GetIdeaByName(SqlCommand command, string ideaName)
        {
            DataTable ideasFromTable = new DataTable();
            string querysql_idea = "SELECT * FROM [Idea] WHERE IdeaName = @IdeaName and IsDeleted = 0";
            QueryParameter[] paramList_idea = new QueryParameter[1];
            paramList_idea[0] = new QueryParameter("@IdeaName", ideaName, DbType.String);
            ideasFromTable = _dataAccess.GetTable(command, querysql_idea, paramList_idea);
            if (ideasFromTable.Rows.Count > 0)
            {
                DataRow row = ideasFromTable.Rows[0];
                IdeaDE ideaDE = new IdeaDE()
                {
                    IdeaId = Convert.ToInt32(row["IdeaId"]),
                    IdeaName = Convert.ToString(row["IdeaName"]),
                    IdeaDescription = Convert.ToString(row["IdeaDescription"]),
                    CreateTime = Convert.ToDateTime(row["CreateTime"]),
                    IsDeleted = Convert.ToBoolean(row["IsDeleted"]),
                    UserId = Convert.ToInt32(row["UserId"]),
                };
                return ideaDE;
            }
            return null;
        }

        /// <summary>
        /// Create an IdeaDE entity.
        /// </summary>
        /// <param name="ideaDE"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public int CreateIdea(SqlCommand command, IdeaDE ideaDE)
        {
            string insertsql_idea = "INSERT INTO [Idea] (IdeaName,IdeaDescription,CreateTime,IsDeleted,UserId) VALUES (@ideaName, @ideaDescription, @createTime, @isDeleted, @uid);select scope_identity()";
            QueryParameter[] paramList_idea = new QueryParameter[5];
            paramList_idea[0] = new QueryParameter("@ideaName", ideaDE.IdeaName, DbType.String);
            paramList_idea[1] = new QueryParameter("@ideaDescription", ideaDE.IdeaDescription, DbType.String);
            paramList_idea[2] = new QueryParameter("@createTime", ideaDE.CreateTime, DbType.DateTime);
            paramList_idea[3] = new QueryParameter("@isdeleted", ideaDE.IsDeleted, DbType.Boolean);
            paramList_idea[4] = new QueryParameter("@uid", ideaDE.UserId, DbType.Int32);
            return Convert.ToInt32(_dataAccess.ExecuteScalar(command, insertsql_idea, paramList_idea));
        }

        /// <summary>
        /// Update an ideaDE entity.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="command"></param>
        /// <param name="ideaDE"></param>
        /// <returns></returns>
        public bool UpdateIdea(SqlCommand command, IdeaDE ideaDE)
        {
            string updatesql_idea = "UPDATE [Idea] SET IdeaName = @ideaName, IdeaDescription = @ideaDescription WHERE IdeaId = @ideaId";

            QueryParameter[] paramList_idea = new QueryParameter[3];
            paramList_idea[0] = new QueryParameter("@ideaName", ideaDE.IdeaName, DbType.String);
            paramList_idea[1] = new QueryParameter("@ideaDescription", ideaDE.IdeaDescription, DbType.String);
            paramList_idea[2] = new QueryParameter("@ideaId", ideaDE.IdeaId, DbType.Int32);
            return Convert.ToBoolean(_dataAccess.ExecuteNonQuery(command, updatesql_idea, paramList_idea));
        }

        /// <summary>
        /// Delete an idea by ideaid.
        /// </summary>
        /// <param name="ideaId"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public bool DeleteIdeaById(SqlCommand command, int ideaId)
        {
            string deletesql_idea = "UPDATE [Idea] SET IsDeleted = 1 WHERE IdeaId = @ideaId";
            QueryParameter[] paramList_idea = new QueryParameter[1];
            paramList_idea[0] = new QueryParameter("@ideaId", ideaId, DbType.Int32);
            return Convert.ToBoolean(_dataAccess.ExecuteNonQuery(command, deletesql_idea, paramList_idea));
        }
    }
}
